package com.xiaoxiao.springbootstudy.mapper;

import com.xiaoxiao.springbootstudy.dto.HisData;
import com.xiaoxiao.springbootstudy.pojo.Order;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xiaoxiao.springbootstudy.pojo.vo.CountResult;
import com.xiaoxiao.springbootstudy.pojo.vo.MonthOrder;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author XiaoXiao
 * @description 针对表【t_order】的数据库操作Mapper
 * @createDate 2025-05-13 14:45:56
 * @Entity com.xiaoxiao.springbootstudy.pojo.Order
 */

public interface OrderMapper extends BaseMapper<Order> {
    @Select("SELECT cust_id custId, SUM(pay_money) as hisTotal FROM `t_order` GROUP BY cust_id ${ew.customSqlSegment}")
    public List<HisData> queryHisDataMapper();

    @Select({"<script>",
            "select ord.*, cust.cust_name custName,",
            "item.item_name itemName from t_customer cust",
            "inner join t_order ord on cust.id=ord.cust_id",
            "inner join t_item item on item.id=ord.item_id",
            "<where>",
            "<if test=\"id != null\">",
            "ord.id = #{id} ",
            "</if>",
            "<if test=\"custName!=null and state!=''\">",
            "and state=#{state} ",
            "</if>",
            "<if test=\"custName!=null and custName!=''\">",
            "and cust_name=#{custName} ",
            "</if>",
            "<if test=\"itemName!=null and itemName!=''\">",
            "and item_name=#{itemName} ",
            "</if>",
            "</where>",
            "</script>"
    })
    public List<Order> queryOrderListMapper(Order order);

    @Select("""
            SELECT DATE_FORMAT(order_date, '%Y-%m') `date`, SUM(pay_money) `money` FROM `t_order` GROUP BY `date`  ORDER BY `date`
            """)
    public List<MonthOrder> queryYearMonthOrderMapper();

    @Select("SELECT DATE_FORMAT(order_date, '%Y-%m') `date`, SUM(num) `value` FROM `t_order` GROUP BY `date`  ORDER BY `date`")
    public List<CountResult> queryYearMonthCountMapper();

    @Select("""
            SELECT
                CONCAT(#{year}, '-', LPAD(month, 2, '0')) AS `name`,
                COALESCE(SUM(t.num), 0) AS `value`
            FROM (
                SELECT @a:=@a + 1 AS month
                FROM mysql.help_topic, (SELECT @a:=0) temp
                LIMIT 12
            ) AS months
            LEFT JOIN
                t_order t
            ON
                YEAR(t.order_date) = #{year} AND MONTH(t.order_date) = months.month
            GROUP BY
                months.month
            ORDER BY
                months.month;
            """)
    public List<CountResult> querySellNumMapper(String year);
}




